﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
    public class TransactionDBAccess
    {

        /// <summary>
        /// Insert Transaction
        /// </summary>
        /// <param name="objTransaction"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool AddNewTransaction(Transaction objTransaction, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {                
                //new SqlParameter("@TransactionID", objTransaction.TransactionID),
                new SqlParameter("@Created", objTransaction.Created),
                new SqlParameter("@Updated",objTransaction.Updated),
                new SqlParameter("@AccountNumberTo", objTransaction.AccountNumberTo),
                new SqlParameter("@AccountNumber", objTransaction.AccountNumber),
                new SqlParameter("@currency", objTransaction.Currency),
                new SqlParameter("@amountTransaction", objTransaction.AmountTransaction),
                new SqlParameter("@action", objTransaction.Acction),
                new SqlParameter("@balance", objTransaction.Balance),
                new SqlParameter("@UserID", objTransaction.UserID),
                new SqlParameter("@errormessage", objTransaction.ErrorMessage),
                new SqlParameter("@TransactionCategoriesID", objTransaction.TransactionCategoriesID),
                new SqlParameter("@DateTrans", objTransaction.DateTrans),
                new SqlParameter("@Status", objTransaction.Status),
                new SqlParameter("@Descriptions", objTransaction.Descriptions)
            };

            return SqlDBHelper.ExecuteNonQuery("AddNewTransaction", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }

        /// <summary>
        /// Update Transaction
        /// </summary>
        /// <param name="objTransaction"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool UpdateTransaction(Transaction objTransaction, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                //new SqlParameter("@TransactionID", objTransaction.TransactionID),
                new SqlParameter("@Created", objTransaction.Created),
                new SqlParameter("@Updated",objTransaction.Updated),
                new SqlParameter("@AccountNumberTo", objTransaction.AccountNumberTo),
                new SqlParameter("@AccountNumber", objTransaction.AccountNumber),
                new SqlParameter("@AmountTransaction", objTransaction.AmountTransaction),
                new SqlParameter("@Acction", objTransaction.Acction),
                new SqlParameter("@Balance", objTransaction.Balance),
                new SqlParameter("@ErrorMessage", objTransaction.ErrorMessage),
                new SqlParameter("@TransactionCategoriesID", objTransaction.TransactionCategoriesID),
                new SqlParameter("@DateTrans", objTransaction.DateTrans),
                new SqlParameter("@Status", objTransaction.Status),
                new SqlParameter("@Descriptions", objTransaction.Descriptions)
            };

            return SqlDBHelper.ExecuteNonQuery("UpdateTransaction", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }


        /// <summary>
        /// Update Status Transaction
        /// </summary>
        /// <param name="TransactionID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool UpdateStatusTransaction(string TransactionID, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@TransactionID", TransactionID),
                
            };

            return SqlDBHelper.ExecuteNonQuery("UpdateStatusTransaction", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }


        /// <summary>
        /// Delete Transaction
        /// </summary>
        /// <param name="TransactionID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool DeleteTransaction(int TransactionID, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@TransactionID", TransactionID)
            };

            return SqlDBHelper.ExecuteNonQuery("DeleteTransaction", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }

        /// <summary>
        /// get Transaction table
        /// </summary>
        /// <param name="AccountNumber"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public DataTable GetTransactionTable(string AccountNumber, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@AccountNumber", AccountNumber)
            };

            return SqlDBHelper.ExecuteParamerizedSelectCommand("GetTransactionTable", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }


        /// <summary>
        /// get All Transaction table
        /// </summary>
        /// <param name="AccountNumber"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public DataTable GetAllTransactionTable(string AccountNumber, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@AccountNumber", AccountNumber)
            };

            return SqlDBHelper.ExecuteParamerizedSelectCommand("GetAllTransactionTable", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }


        /// <summary>
        /// Get Transaction Details
        /// </summary>
        /// <param name="TransactionID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public Transaction GetTransactionDetails(int TransactionID, String CONNECTION_STRING)
        {
            Transaction objTransaction = null;

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@TransactionID",TransactionID),
            };
            //Lets get the list of all Transaction in a datataable
            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetTransactionDetails", CommandType.StoredProcedure, CONNECTION_STRING, parameters))
            {
                //check if any record exist or not
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];

                    //Lets go ahead and create the list of Transaction
                    objTransaction = new Transaction();

                    //Now lets populate Transaction details into the list of Transaction                                         
                    objTransaction.TransactionID = Convert.ToInt32(row["TransactionID"]);
                    objTransaction.Created = row["Created"].ToString();
                    objTransaction.Updated = row["Updated"].ToString();
                    objTransaction.AccountNumberTo = row["AccountNumberTo"].ToString();
                    objTransaction.AccountNumber = row["AccountNumber"].ToString();
                    objTransaction.AmountTransaction = Convert.ToDouble(row["AmountTransaction"].ToString());
                    objTransaction.Currency = row["Currency"].ToString();
                    objTransaction.Acction = row["Action"].ToString();
                    objTransaction.Balance = Convert.ToDouble(row["Balance"].ToString());
                    objTransaction.ErrorMessage = row["ErrorMessage"].ToString();
                    objTransaction.TransactionCategoriesID = Convert.ToInt32(row["TransactionCategoriesID"].ToString());
                    objTransaction.DateTrans = row["DateTrans"].ToString();
                    objTransaction.Status = row["Status"].ToString();
                    objTransaction.Descriptions = row["Descriptions"].ToString();
                }
            }

            return objTransaction;
        }


        /// <summary>
        /// Get Transaction List
        /// </summary>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public List<Transaction> GetTransactionList(String CONNECTION_STRING)
        {
            List<Transaction> listTransaction = null;

            //Lets get the list of all Transaction in a datataable
            using (DataTable table = SqlDBHelper.ExecuteSelectCommand("GetTransactionList", CommandType.StoredProcedure, CONNECTION_STRING))
            {
                //check if any record exist or not
                if (table.Rows.Count > 0)
                {
                    //Lets go ahead and create the list of Transaction
                    listTransaction = new List<Transaction>();

                    //Now lets populate Transaction details into the list of Transaction
                    foreach (DataRow row in table.Rows)
                    {
                        Transaction objTransaction = new Transaction();
                        objTransaction.TransactionID = Convert.ToInt32(row["TransactionID"]);
                        objTransaction.Created = row["Created"].ToString();
                        objTransaction.Updated = row["Updated"].ToString();
                        objTransaction.AccountNumberTo = row["AccountNumberTo"].ToString();
                        objTransaction.AccountNumber = row["AccountNumber"].ToString();
                        objTransaction.AmountTransaction = Convert.ToDouble(row["AmountTransaction"].ToString());
                        objTransaction.Currency = row["Currency"].ToString();
                        objTransaction.Acction = row["Action"].ToString();
                        objTransaction.Balance = Convert.ToDouble(row["Balance"].ToString());
                        objTransaction.ErrorMessage = row["ErrorMessage"].ToString();
                        objTransaction.TransactionCategoriesID = Convert.ToInt32(row["TransactionCategoriesID"].ToString());
                        objTransaction.DateTrans = row["DateTrans"].ToString();
                        objTransaction.Status = row["Status"].ToString();
                        objTransaction.Descriptions = row["Descriptions"].ToString();

                        listTransaction.Add(objTransaction);
                    }
                }
            }

            return listTransaction;
        }        
    }
}
